Add a New Excel Data Connection

Note: Variables can be used to populate data connection settings (for example: the port number variable [[EBSDB_PORTNUMBER]], replaces the port number value).

To add a new Microsoft Excel data connection:

1. Click the Dashboard Configuration button on the dashboard ribbon.

   

   The Dashboard screen is displayed.

   

2. Click the Data Connections tab.

   

3. Click the Add button to add a new connection.

   

   The new connection screen is displayed.

   

4. Enter the relevant details.

The fields on the Connections tab are described in the following table.

This button	Does this...
Data Connection Title	The name assigned to the data connection. This field is mandatory.
Category	The data connection category. Choose from the drop-down list.
Data Source Type	The data source type. Choose from the drop-down list.
Host Name	The host name. Variable: [[EBSDB_HOSTNAME]]
Port Number	The port number. Variable: [[EBSDB_PORTNUMBER]]
Database Name	The database name. Variable: [[EBSDB_NAME]]
Database Instance	The database instance. Variable: [[EBSDB_INSTANCENAME]]
User Name	The account used to access the database. This field is mandatory. Variable: [[EBSDB_USERNAME]] Note: Elevated permissions are required to create table from a Microsoft Excel data connection in an ebs database.
Password	The password for the account used to access the database.
Database Date Time Format	The date and time format. Variable: [[DEFAULT_DATABASE_DATE_TIME_FORMAT]]

5. Click Save.

   

   A successful connection is indicated in the Connection field. The connection is added to the sidebar menu and the Excel tab is enabled.

   

6. Click the Excel tab.

   

7. Click the Add button.

   

   The File Location field is displayed.

   

8. Enter the location of the file in the File Location field.

   Note: You can currently only use .XLSX files.

9. Click an area of the screen outside the File location field and then click the Validate Location button.

   

   Sheets contained in the spreadsheet are listed to indicated that validation is successful.

   

10. Select the relevant sheets.

    

11. Click Save.

    

Tables and views can be added to your Excel data connection by selecting from a schema list.

Back to top

To add data to a connection to your Excel data connection with Select from Schema:

1. Open the relevant data connection and select the Data tab.

   

2. Click the Add Table button.

   

   The table generation window is displayed where you can add tables or views.

   

3. Click the Select from Schema tab.

   

4. Browse to the dbo table, click the Expand button to reveal the contents.

   

5. Browse the contents of the table prefixed PI_EXCEL and select the relevant check box to include all columns for a table.

   Note: Microsoft Excel tables are prefixed PI_EXCEL.

   

   Alternately, click the Select all Columns button to include all columns contained in the relevant table, or click the Expand button to display a columns list.

   

6. Select the columns that you want to include.

   

   Note: You may have to alter the contents of the Name column if the object has already been included in another item in the data connection (for example: change 'Field name' to 'Fieldname').

7. Click the Generate button in the bottom right corner.

   

   The tables are added to the connection.

   

8. Click Save.

Back to top